﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Thaire.Investment.BL
{
    public class ReportQuery
    {
     public   static string loanQuery = @"


SELECT 
--HEADER
LOANS_HEADER.[LOANS_HEADER_ID]
,LOANS_HEADER.[COMPANY_NAME] ----AS 'Company'
--,LOANS_HEADER.[COMPANY_ID]
,LOANS_HEADER.[MATURITY_STATUS] ----AS 'Maturity Status'
--,LOANS_HEADER.[LOAN_TYPE_ID]
,LOANS_HEADER.[LOAN_TYPE_NAME] ----AS 'Loan Type'
,LOANS_HEADER.[CONTRACT_NUMBER] ----AS 'Contract Number'
,(select REFERENCE_CODE_KEY from REFERENCE_CODE where REFERENCE_CODE_DOMAIN='LOAN_OBJECTIVE' and REFERENCE_CODE_VALUE_EN = LOANS_HEADER.[LOAN_OBJECTIVE_ID] ) as LOAN_OBJECTIVE_ID
,LOANS_HEADER.[BORROWER_NAME] --AS 'ชื่อผู้กู้'
,LOANS_HEADER.[BORROWER_OCCUPATION_NAME] --AS 'อาชีพผู้กู้'
--,LOANS_HEADER.[BORROWER_OCCUPATION_ID]
,LOANS_HEADER.[BORROWER_ID_CARD_NO] --AS 'เลขที่บัตรประชาชนผู้กู้'
,LOANS_HEADER.[GUARANTOR_NAME] --AS 'ชื่อผู้ค้ำประกัน'
,LOANS_HEADER.[GUARANTOR_OCUUPATION_NAME] --AS 'อาชีพผู้ค้ำประกัน'
--,LOANS_HEADER.[GUARANTOR_OCUUPATION_ID]
,LOANS_HEADER.[GUARANTOR_ID_CARD_NO] --AS 'เลขที่บัตรประชาชนผู้ค้ำประกัน'
--,LOANS_HEADER.[GUARANTEE_TYPE_ID] --AS 'ประเภทหลักประกัน'
,(select REFERENCE_CODE_KEY from REFERENCE_CODE where REFERENCE_CODE_DOMAIN='GUARANTEE_TYPE' and REFERENCE_CODE_VALUE_EN = LOANS_HEADER.[GUARANTEE_TYPE_ID] ) as GUARANTEE_TYPE_ID
,LOANS_HEADER.[MORTGAGOR_NAME_1] --AS 'ชื่อผู้จำนอง (1)'
,LOANS_HEADER.[MORTGAGOR_NAME_2] --AS 'ชื่อผู้จำนอง (2)'
,LOANS_HEADER.[MORTGAGOR_NAME_3] --AS 'ชื่อผู้จำนอง (3)'
,(select REFERENCE_CODE_KEY from REFERENCE_CODE where REFERENCE_CODE_DOMAIN='ASSESSMENT_MORTGATE' and REFERENCE_CODE_VALUE_EN = LOANS_HEADER.[ASSESSMENT_MORTGATE_ID] ) as ASSESSMENT_MORTGATE_ID
,LOANS_HEADER.[TITLE_DEED_MAPSHEET_NO] --AS 'ตำแหน่งที่ดิน-ระวาง'
,LOANS_HEADER.[TITLE_DEED_PARCEL_NO] --AS 'ตำแหน่งที่ดิน-เลขที่ดิน'
,LOANS_HEADER.[TITLE_DEED_DEALING_FILE_NO] --AS 'ตำแหน่งที่ดิน-หน้าสำรวจ'
,LOANS_HEADER.[TITLE_DEED_NO] --AS 'โฉนดที่ดิน-เลขที่'
,LOANS_HEADER.[TITLE_DEED_VOLUMN] --AS 'โฉนดที่ดิน-เล่ม'
,LOANS_HEADER.[TITLE_DEED_PAGE] --AS 'โฉนดที่ดิน-หน้า'
,LOANS_HEADER.[TITLE_DEED_PROVINCE_NAME] --AS 'โฉนดที่ดิน-จังหวัด'
--,LOANS_HEADER.[TITLE_DEED_PROVINCE_ID]
,LOANS_HEADER.[TITLE_DEED_DISTRICT_NAME] --AS 'โฉนดที่ดิน-อำเภอ'
--,LOANS_HEADER.[TITLE_DEED_DISTRICT_ID]
,LOANS_HEADER.[TITLE_DEED_SUB_DISTRICT_NAME] --AS 'โฉนดที่ดิน-ตำบล'
--,LOANS_HEADER.[TITLE_DEED_SUB_DISTRICT_ID]
,LOANS_HEADER.[TITLE_DEED_TOTAL_AREA] --AS 'โฉนดที่ดิน-เนื้อที่ (ไร่-งาน-ตร.วา)'
,LOANS_HEADER.[APARTMENT_NO] --AS 'ที่ตั้งห้องชุด-ห้องชุดเลขที่'
,LOANS_HEADER.[APARTMENT_FLOOR] --AS 'ที่ตั้งห้องชุด-ชั้นที่'
,LOANS_HEADER.[APARTMENT_BUILDING_NO] --AS 'ที่ตั้งห้องชุด-อาคารเลขที่'
,LOANS_HEADER.[APARTMENT_BUILDING_NAME] --AS 'ที่ตั้งห้องชุด-ชื่ออาคารชุด'
,LOANS_HEADER.[REGISTER_BUILDING_NO] --AS 'ที่ตั้งห้องชุด-ทะเบียนอาคารชุดเลขที่'
,LOANS_HEADER.[MORTGAGE_DATE] --AS 'วันที่จำนอง'
,LOANS_HEADER.[CONTRACT_START_DATE] --AS 'ระยะเวลาเริ่มต้น'
,LOANS_HEADER.[CONTRACT_END_DATE] --AS 'ระยะเวลาสิ้นสุด'
,LOANS_HEADER.[ASSESSMENT_DATE] --AS 'วันที่ประเมินราคาทรัพย์สินที่จำนอง'
,LOANS_HEADER.[ASSESSMENT_COST] --AS 'ราคาประเมิน'
,LOANS_HEADER.[ASSET_VALUE] --AS 'มูลค่าทรัพย์สิน/วงเงินค้ำประกัน'
,LOANS_HEADER.[CONTRACT_AMOUNT] --AS 'จำนวนเงินตามสัญญา '
,LOANS_HEADER.[LIQUIDITY_TIER_I_PERCENT] --AS '%Liquidity(Tier I)'
,LOANS_HEADER.[LIQUIDITY_TIER_II_PERCENT] --AS '%Liquidity(Tier II)'
,LOANS_HEADER.[LIQUIDITY_LOW_LIQUIDITY_PERCENT] --AS '%Liquidity(Low Liquidity)'
--,LOANS_HEADER.[LIQUIDITY]
,LOANS_HEADER.[INVOLVED_PARTY_TYPE_NAME] --AS 'Involved Party Type'
,(select INVOLVED_PARTY_TYPE_CODE from dbo.INVOLVED_PARTY_TYPE IPT where IPT.INVOLVED_PARTY_TYPE_ID= LOANS_HEADER.INVOLVED_PARTY_TYPE_ID) AS INVOLVED_PARTY_TYPE_CODE --AS 'Involved Party Type No' --not found field
,LOANS_HEADER.[INSURANCE_NO] --AS 'หมายเลข กธ.'
,LOANS_HEADER.[PROTECTION_START_DATE] --AS 'วันเริ่มคุ้มครอง'
,LOANS_HEADER.[PROTECTION_END_DATE] --AS 'วันสิ้นสุดความคุ้มครอง'
,LOANS_HEADER.[CREATE_DATE] AS HEADER_CREATE_DATE
,LOANS_HEADER.[CREATE_BY] AS HEADER_CREATE_BY
,LOANS_HEADER.[MODIFY_DATE] AS HEADER_MODIFY_DATE
,LOANS_HEADER.[MODIFY_BY] AS HEADER_MODIFY_BY
--,LOANS_HEADER.[INVOLVED_PARTY_TYPE_ID]
,ROUND( CAST(DATEDIFF(month,GETDATE(), LOANS_HEADER.CONTRACT_END_DATE)AS FLOAT)/CAST(12 AS FLOAT),2 ) AS TIME_TO_MATURITY

--INTEREST
--,LOANS_INTEREST_DETAIL.[LOANS_INTEREST_DETAIL_ID]
--,LOANS_INTEREST_DETAIL.[INTEREST_HEADER_ID]
,LOANS_INTEREST_DETAIL.[INTEREST_PERIOD] --AS 'Period'
,LOANS_INTEREST_DETAIL.[MLR_DATE] --AS 'MLR Rate Date'
,LOANS_INTEREST_DETAIL.[MLR_RATE] --AS 'MLR Rate'
,LOANS_INTEREST_DETAIL.[MLR_MINUS] --AS 'MLR (-)'
,LOANS_INTEREST_DETAIL.[INTEREST_RATE] --AS 'INTEREST RATE'
,LOANS_INTEREST_DETAIL.[UPDATE_INFO] --AS 'Update Info'
,LOANS_INTEREST_DETAIL.[INSTALLMENT] --AS 'Installment'
,LOANS_INTEREST_DETAIL.[CREATE_DATE] AS INTEREST_CREATE_DATE
,LOANS_INTEREST_DETAIL.[CREATE_BY] AS INTEREST_CREATE_BY
,LOANS_INTEREST_DETAIL.[MODIFY_DATE] AS INTEREST_MODIFY_DATE
,LOANS_INTEREST_DETAIL.[MODIFY_BY] AS INTEREST_MODIFY_BY
--EXTRA INSTALMENT
--,LOANS_EXTRAINSTALLMENT.[LOANS_EXTRAINSTALLMENT_ID]
--,LOANS_EXTRAINSTALLMENT.[LOANS_HEADER_ID]
--,LOANS_EXTRAINSTALLMENT.[INTEREST_ID]
--,LOANS_EXTRAINSTALLMENT.[AMORTIZE_ID]
,LOANS_EXTRAINSTALLMENT.[NEW_INSTALLMENT] --AS 'New Installment Rate'
,LOANS_EXTRAINSTALLMENT.[EXTRAINSTALLMENT_DATE] --AS 'Extra Installment Date'
,LOANS_EXTRAINSTALLMENT.[EXTRAINSTALLMENT_RATE] --AS 'Extra Installment Rate'
,LOANS_EXTRAINSTALLMENT.[CLOSE_ACCOUNT_DATE] --AS 'Close Account Date'
,LOANS_EXTRAINSTALLMENT.[CLOSE_ACCOUNT_INTEREST] --AS 'Close Account Interest'
,LOANS_EXTRAINSTALLMENT.[CLOSE_ACCOUNT_PRINCIPLE] --AS 'Close Account Principle'
,LOANS_EXTRAINSTALLMENT.[CLOSE_ACCOUNT_TOTAL] --AS 'Close Account Total'
,LOANS_EXTRAINSTALLMENT.[CREATE_DATE] AS EXTRA_INSTALLMENT_CREATE_DATE
,LOANS_EXTRAINSTALLMENT.[CREATE_BY] AS EXTRA_INSTALLMENT_CREATE_BY
,LOANS_EXTRAINSTALLMENT.[MODIFY_DATE] AS EXTRA_INSTALLMENT_MODIFY_DATE
,LOANS_EXTRAINSTALLMENT.[MODIFY_BY] AS EXTRA_INSTALLMENT_MODIFY_BY
  FROM LOANS_HEADER
  left join LOANS_INTEREST_DETAIL on LOANS_HEADER.LOANS_HEADER_ID = LOANS_INTEREST_DETAIL.INTEREST_HEADER_ID
  left join LOANS_EXTRAINSTALLMENT on LOANS_HEADER.LOANS_HEADER_ID = LOANS_EXTRAINSTALLMENT.LOANS_HEADER_ID
  where 1=1 
 

";

     public static string queryDepositFinance = @"




SELECT  
		-----
		DEPOSIT_HEADER.DEPOSIT_HEADER_ID,
		-----
		DEPOSIT_HEADER.[DEPOSIT_COMPANY_NAME]	,
		DEPOSIT_HEADER.[MATURITY_STATUS]	,
		--DEPOSIT_HEADER.[ASSET_CLASS_ID]	,
        (select asset.ASSET_CLASS_EN from ASSET_CLASS as asset  where asset.ASSET_CLASS_ID = DEPOSIT_HEADER.[ASSET_CLASS_ID]) ASSET_CLASS_ID,
		DEPOSIT_HEADER.[ACCOUNT_VOUCHER_NO]	,
		DEPOSIT_HEADER.[TERM]	,
		(select REFERENCE_CODE_VALUE_EN from REFERENCE_CODE 
		where REFERENCE_CODE_DOMAIN='DEPOSIT_TYPE' and REFERENCE_CODE_ID =DEPOSIT_HEADER.FINANCIAL_INSTRUMENT_TYPE_ID ) AS FINANCIAL_INSTRUMENT_TYPE_ID,
		DEPOSIT_HEADER.[BANK_NAME]	,
		DEPOSIT_HEADER.[BRANCH_NAME]	,
		DEPOSIT_HEADER.[BANK_CODE]	,
		DEPOSIT_HEADER.[SYMBOL]	,
		DEPOSIT_HEADER.[MATURITY_DATE]	,
        ROUND( CAST(DATEDIFF(month, DEPOSIT_HEADER.[MATURITY_DATE], GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 ) AS MATURITY_DATE_CAL,
		DEPOSIT_HEADER.[INTEREST_PAYMENT_DATE]	,
		DEPOSIT_HEADER.[INTEREST_RATE]	,
		DEPOSIT_HEADER.[GUARANTOR]	,
		DEPOSIT_HEADER.[LIQUIDITY_TIER_I_PERCENT]	,
		DEPOSIT_HEADER.[LIQUIDITY_TIER_II_PERCENT]	,
		DEPOSIT_HEADER.[LIQUIDITY_LOW_LIQUIDITY_PERCENT]	,
		
		(SELECT ISSUE_RATING FROM RATING_HISTORY 
		WHERE RATING_HISTORY_ID = (SELECT MAX(RATING_HISTORY_ID) FROM RATING_HISTORY 
		WHERE FINANCIAL_INSTRUMENT_TYPE = 'DEPOSIT' AND FINANCIAL_INSTRUMENT_ID = DEPOSIT_HEADER.DEPOSIT_HEADER_ID)) AS ISSUE_RATING	,
		
    (SELECT ISSUE_CREDIT_AGENCY FROM RATING_HISTORY 
		WHERE RATING_HISTORY_ID = (SELECT MAX(RATING_HISTORY_ID) FROM RATING_HISTORY 
		WHERE FINANCIAL_INSTRUMENT_TYPE = 'DEPOSIT' AND FINANCIAL_INSTRUMENT_ID = DEPOSIT_HEADER.DEPOSIT_HEADER_ID)) AS ISSUE_CREDIT_AGENCY	,
		(SELECT ISSUE_UPDATE_DATE FROM RATING_HISTORY 
		WHERE RATING_HISTORY_ID = (SELECT MAX(RATING_HISTORY_ID) FROM RATING_HISTORY 
		WHERE FINANCIAL_INSTRUMENT_TYPE = 'DEPOSIT' AND FINANCIAL_INSTRUMENT_ID = DEPOSIT_HEADER.DEPOSIT_HEADER_ID)) AS ISSUE_UPDATE_DATE	,
		(SELECT ISSUE_CREDIT_AGENCY_COUNTRY FROM RATING_HISTORY 
		WHERE RATING_HISTORY_ID = (SELECT MAX(RATING_HISTORY_ID) FROM RATING_HISTORY 
		WHERE FINANCIAL_INSTRUMENT_TYPE = 'DEPOSIT' AND FINANCIAL_INSTRUMENT_ID = DEPOSIT_HEADER.DEPOSIT_HEADER_ID)) AS ISSUE_CREDIT_AGENCY_COUNTRY	,
		
		(SELECT ISSUER_RATING FROM RATING_HISTORY 
		WHERE RATING_HISTORY_ID = (SELECT MAX(RATING_HISTORY_ID) FROM RATING_HISTORY 
		WHERE FINANCIAL_INSTRUMENT_TYPE = 'DEPOSIT' AND FINANCIAL_INSTRUMENT_ID = DEPOSIT_HEADER.DEPOSIT_HEADER_ID)) AS ISSUER_RATING	,
	
    (SELECT ISSUER_CREDIT_AGENCY FROM RATING_HISTORY 
		WHERE RATING_HISTORY_ID = (SELECT MAX(RATING_HISTORY_ID) FROM RATING_HISTORY 
		WHERE FINANCIAL_INSTRUMENT_TYPE = 'DEPOSIT' AND FINANCIAL_INSTRUMENT_ID = DEPOSIT_HEADER.DEPOSIT_HEADER_ID)) AS ISSUER_CREDIT_AGENCY	,
		(SELECT ISSUER_UPDATE_DATE FROM RATING_HISTORY 
		WHERE RATING_HISTORY_ID = (SELECT MAX(RATING_HISTORY_ID) FROM RATING_HISTORY 
		WHERE FINANCIAL_INSTRUMENT_TYPE = 'DEPOSIT' AND FINANCIAL_INSTRUMENT_ID = DEPOSIT_HEADER.DEPOSIT_HEADER_ID)) AS ISSUER_UPDATE_DATE	,
		(SELECT ISSUER_CREDIT_AGENCY_COUNTRY FROM RATING_HISTORY 
		WHERE RATING_HISTORY_ID = (SELECT MAX(RATING_HISTORY_ID) FROM RATING_HISTORY 
		WHERE FINANCIAL_INSTRUMENT_TYPE = 'DEPOSIT' AND FINANCIAL_INSTRUMENT_ID = DEPOSIT_HEADER.DEPOSIT_HEADER_ID)) AS ISSUER_CREDIT_AGENCY_COUNTRY	,
				
		(SELECT GUARANTOR_RATING FROM RATING_HISTORY 
		WHERE RATING_HISTORY_ID = (SELECT MAX(RATING_HISTORY_ID) FROM RATING_HISTORY 
		WHERE FINANCIAL_INSTRUMENT_TYPE = 'DEPOSIT' AND FINANCIAL_INSTRUMENT_ID = DEPOSIT_HEADER.DEPOSIT_HEADER_ID)) AS GUARANTOR_RATING	,
		(SELECT GUARANTOR_CREDIT_AGENCY FROM RATING_HISTORY 
		WHERE RATING_HISTORY_ID = (SELECT MAX(RATING_HISTORY_ID) FROM RATING_HISTORY 
		WHERE FINANCIAL_INSTRUMENT_TYPE = 'DEPOSIT' AND FINANCIAL_INSTRUMENT_ID = DEPOSIT_HEADER.DEPOSIT_HEADER_ID)) AS GUARANTOR_CREDIT_AGENCY	,
		(SELECT GUARANTOR_UPDATE_DATE FROM RATING_HISTORY 
		WHERE RATING_HISTORY_ID = (SELECT MAX(RATING_HISTORY_ID) FROM RATING_HISTORY 
		WHERE FINANCIAL_INSTRUMENT_TYPE = 'DEPOSIT' AND FINANCIAL_INSTRUMENT_ID = DEPOSIT_HEADER.DEPOSIT_HEADER_ID)) AS GUARANTOR_UPDATE_DATE	,
		(SELECT GUARANTOR_CREDIT_AGENCY_COUNTRY FROM RATING_HISTORY 
		WHERE RATING_HISTORY_ID = (SELECT MAX(RATING_HISTORY_ID) FROM RATING_HISTORY 
		WHERE FINANCIAL_INSTRUMENT_TYPE = 'DEPOSIT' AND FINANCIAL_INSTRUMENT_ID = DEPOSIT_HEADER.DEPOSIT_HEADER_ID)) AS GUARANTOR_CREDIT_AGENCY_COUNTRY	,
		
		DEPOSIT_HEADER.[INVOLVED_PARTY_TYPE_NAME]	,
		(select INVOLVED_PARTY_TYPE_CODE from INVOLVED_PARTY_TYPE 
		where INVOLVED_PARTY_TYPE.INVOLVED_PARTY_TYPE_NAME= DEPOSIT_HEADER.INVOLVED_PARTY_TYPE_NAME) AS INVOLVED_PARTY_TYPE_CODE,
	
        (select INVOLVED_PARTY_TYPE_REFERENCE from INVOLVED_PARTY_TYPE 
		where INVOLVED_PARTY_TYPE.INVOLVED_PARTY_TYPE_NAME= DEPOSIT_HEADER.INVOLVED_PARTY_TYPE_NAME) AS INVOLVED_PARTY_TYPE_REFERENCE,
		
DEPOSIT_HEADER.[REMAIN_UNIT]	,
		DEPOSIT_TRANSACTION.[TRANSACTION_TYPE]	,
		DEPOSIT_TRANSACTION.[REF_NO]	,
		(select REFERENCE_CODE_VALUE_EN from REFERENCE_CODE 
		where REFERENCE_CODE_DOMAIN='PAYMENT_TYPE' and REFERENCE_CODE_KEY =DEPOSIT_TRANSACTION.TYPEOFPAYMENT_ID ) as TYPEOFPAYMENT_ID,
		DEPOSIT_TRANSACTION.[SETTLEMENT_DATE]	,
		DEPOSIT_TRANSACTION.[AMOUNT]	,
		(SELECT OBLIGATION_NAME FROM OBLIGATION WHERE OBLIGATION.OBLIGATION_ID = DEPOSIT_TRANSACTION.OBLIGATION_NO_ID ) as OBLIGATION_NO_ID,
		DEPOSIT_TRANSACTION.[GUARANTEE_TYPE]	,
		(SELECT OBLIGATION_NO_NAME FROM OBLIGATION_NO WHERE OBLIGATION_NO.OBLIGATION_NO_ID = DEPOSIT_TRANSACTION.OBLIGATION_ID ) as OBLIGATION_ID,
		DEPOSIT_TRANSACTION.[AMOUNT]	,
		DEPOSIT_TRANSACTION.[GUARANTEE_START_DATE]	,
		DEPOSIT_TRANSACTION.[GUARANTEE_END_DATE] 

  FROM DEPOSIT_HEADER
		LEFT join DEPOSIT_TRANSACTION
		on DEPOSIT_HEADER.DEPOSIT_HEADER_ID = DEPOSIT_TRANSACTION.DEPOSIT_HEADER_ID
where 1=1 
";

        //JANE
     public static string queryEquity = @"


select --HEADER
EQUITY_HEADER.EQUITY_HEADER_ID
,EQUITY_HEADER.[EQUITY_COMPANY_NAME]--AS 'Company Name'
,EQUITY_HEADER.[MATURITY_STATUS]--AS 'Maturity Status'
,EQUITY_HEADER.[FULL_NAME_EN]--AS 'Full Name (EN)'
,EQUITY_HEADER.[FULL_NAME_TH]--AS 'Full Name (TH)'
,EQUITY_HEADER.[EQUITY_TYPE_NAME]--AS 'Equity Type'
,EQUITY_HEADER.[EQUITY_SECTOR_GROUP]--AS 'Sector Group'
,EQUITY_HEADER.[EQUITY_SECTOR]--AS 'Sector'
,EQUITY_HEADER.[SYMBOL]--AS 'Symbol'
--,EQUITY_HEADER.[LISTED_STATUS]--AS 'Listed Status'
,(select REFERENCE_CODE_KEY from REFERENCE_CODE where REFERENCE_CODE_DOMAIN='RELATED_NONRELATED' and REFERENCE_CODE_VALUE_EN = EQUITY_HEADER.[LISTED_STATUS] ) as LISTED_STATUS
,EQUITY_HEADER.[BROKER]--AS 'Broker'
,EQUITY_HEADER.[CAPITAL_REGISTERED]--AS 'Capital Registered'
,EQUITY_HEADER.[CAPITAL_PAID]--AS 'Capital Paid'
,EQUITY_HEADER.[INVOLVED_PARTY_TYPE_NAME]--AS 'Involved Party Type Name'

,(select INVOLVED_PARTY_TYPE_CODE from dbo.INVOLVED_PARTY_TYPE IPT where IPT.INVOLVED_PARTY_TYPE_NAME= EQUITY_HEADER.INVOLVED_PARTY_TYPE_NAME) AS INVOLVED_PARTY_TYPE_CODE

,(select INVOLVED_PARTY_TYPE_REFERENCE from dbo.INVOLVED_PARTY_TYPE IPT where IPT.INVOLVED_PARTY_TYPE_NAME= EQUITY_HEADER.INVOLVED_PARTY_TYPE_NAME) AS INVOLVED_PARTY_TYPE_REFERENCE

,EQUITY_HEADER.[REMAIN_UNIT]--AS 'Remain Unit'
,EQUITY_HEADER.[BID_PRICE_DATE]--AS 'Bid Price Date'
,EQUITY_HEADER.[BID_PRICE_PER_UNIT]--AS 'Bid Price per Unit'
,0 AS BID_AMOUNT--not found field
,EQUITY_HEADER.[BV_PRICE_DATE]--AS 'BV Price Date'
,EQUITY_HEADER.[BV_PRICE_PER_UNIT]--AS 'BV Price per Unit'
,0 AS BV_AMOUNT--AS 'BV AMOUNT'--not found field
,EQUITY_HEADER.[FV_PRICE_DATE]--AS 'FV Price Date'
,EQUITY_HEADER.[FV_PRICE_PER_UNIT]--AS 'FV Price per Unit'
,0 AS FV_AMOUNT--AS 'FV AMOUNT'--not found field
,EQUITY_HEADER.[CLOSE_PRICE_DATE]--AS 'Close Price Date'
,EQUITY_HEADER.[CLOSE_PRICE_PER_UNIT]--AS 'Close Price per Unit'
,EQUITY_HEADER.[CLOSE_PRICE_PER_UNIT]--AS 'Close AMOUNT'--not found field
,EQUITY_HEADER.[LIQUIDITY_TIER_I_PERCENT]--AS '%Liquidity(Tier I)'
,EQUITY_HEADER.[LIQUIDITY_TIER_II_PERCENT]--AS '%Liquidity(Tier II)'
,EQUITY_HEADER.[LIQUIDITY_LOW_LIQUIDITY_PERCENT]--AS '%Liquidity(Low Liquidity)'
,(select REFERENCE_CODE_VALUE_EN from REFERENCE_CODE where REFERENCE_CODE_DOMAIN='RELATED_NONRELATED' and REFERENCE_CODE_ID =EQUITY_HEADER.[RELATED_ID] ) as RELATED_ID
,EQUITY_HEADER.[REMARK_OIC]--AS 'Remark (OIC)'
,EQUITY_HEADER.[CREATE_DATE] AS 'HEADER_CREATE_DATE'
,EQUITY_HEADER.[CREATE_BY] AS 'HEADER_CREATE_BY'
,EQUITY_HEADER.[MODIFY_DATE] AS 'HEADER_MODIFY_DATE'
,EQUITY_HEADER.[MODIFY_BY] AS 'HEADER_MODIFY_BY'

-- TRANSACTION
,EQUITY_TRANSACTION.[TRANSACTION_TYPE]--AS 'Transaction Type'
,EQUITY_TRANSACTION.[REF_NO]--AS 'Ref. No.'
--,EQUITY_TRANSACTION.[TYPEOFPAYMENT_ID]--AS 'Type of Payment'
,(select REFERENCE_CODE_VALUE_EN from REFERENCE_CODE where REFERENCE_CODE_DOMAIN='PAYMENT_TYPE' and REFERENCE_CODE_KEY =EQUITY_TRANSACTION.TYPEOFPAYMENT_ID ) as TYPEOFPAYMENT_ID
,EQUITY_TRANSACTION.[TRADE_DATE]--AS 'Trade Date'
,EQUITY_TRANSACTION.[SETTLEMENT_DATE]--AS 'Settlement Date'
,EQUITY_TRANSACTION.[UNIT]--AS 'Unit'
,EQUITY_TRANSACTION.[PAR_UNIT]--AS 'Par / Unit'
,EQUITY_TRANSACTION.[PAR_AMOUNT]--AS 'Par Amount'
,EQUITY_TRANSACTION.[PRICE_UNIT]--AS 'Price / Unit'
,EQUITY_TRANSACTION.[GROSS_AMOUNT]--AS 'Gross Amount'
,EQUITY_TRANSACTION.[COM_RATE]--AS 'COM. Rate'
,EQUITY_TRANSACTION.[COM_AMOUNT]--AS 'COM AMOUNT'
,EQUITY_TRANSACTION.[VAT_RATE]--AS 'VAT Rate'
,EQUITY_TRANSACTION.[VAT_AMOUNT]--AS 'VAT AMOUNT'
,EQUITY_TRANSACTION.[PURCHASE_VALUE_NET]--AS 'Purchase Value (Net)'
,EQUITY_TRANSACTION.[WH_TAX_RATE]--AS 'W/H TAX Rate'
,EQUITY_TRANSACTION.[WH_TAX_AMOUNT]--AS 'W/H TAX AMOUNT'
,EQUITY_TRANSACTION.[NET_SETTLE]--AS 'Net Settle AMT. incl W/T'
,(select REFERENCE_CODE_VALUE_EN from REFERENCE_CODE where REFERENCE_CODE_DOMAIN='PHYSICAL' and REFERENCE_CODE_ID =EQUITY_TRANSACTION.PHYSICAL_ID ) as PHYSICAL_ID
,(select REFERENCE_CODE_VALUE_EN from REFERENCE_CODE where REFERENCE_CODE_DOMAIN='KEEP_AT' and REFERENCE_CODE_ID =EQUITY_TRANSACTION.KEEP_AT_ID ) as KEEP_AT_ID
,(SELECT OBLIGATION_NO_NAME FROM OBLIGATION_NO WHERE OBLIGATION_NO.OBLIGATION_NO_ID = EQUITY_TRANSACTION.OBLIGATION_ID ) as OBLIGATION_ID
,(SELECT OBLIGATION_NAME FROM OBLIGATION WHERE OBLIGATION.OBLIGATION_ID = EQUITY_TRANSACTION.OBLIGATION_NO_ID ) as OBLIGATION_NO_ID
,EQUITY_TRANSACTION.[GUARANTEE_START_DATE]--AS 'Start Date'
,EQUITY_TRANSACTION.[GUARANTEE_END_DATE]--AS 'End Date'
,EQUITY_TRANSACTION.[CREATE_DATE] AS 'TRANS_CREATE_DATE'
,EQUITY_TRANSACTION.[CREATE_BY] AS 'TRANS_CREATE_BY'
,EQUITY_TRANSACTION.[MODIFY_DATE] AS 'TRANS_MODIFY_DATE'
,EQUITY_TRANSACTION.[MODIFY_BY] AS 'TRANS_MODIFY_BY'
----PRIVILEGE

,'' AS 'PRIVILEGE_TYPE_NAME'
,'' AS 'EX_DATE'
,'' AS 'BCD_DATE'
,0 AS 'DIV_UNIT'
,0 AS 'DIV_AMOUNT'
,0 AS 'DIV_STOCK'
,0 AS 'FRACTION_UNIT'
,0 AS 'FRACTION_DIV_UNIT'
,0 AS 'AMORTIZE_UNIT'
,0 AS 'PRICE_UNIT'
,0 AS 'RIGHT_OLD_NEW'
,'' AS 'PAYMENT_DATE'
,'' AS 'TRANNSAFER_DATE'
,0 AS 'TRANNSAFER_PRICE'
,'' AS 'PRIVILEGE_CREATE_DATE'
,'' AS 'PRIVILEGE_CREATE_BY'
,'' AS 'PRIVILEGE_MODIFY_DATE'
,'' AS 'PRIVILEGE_MODIFY_BY'

from EQUITY_HEADER
inner join EQUITY_TRANSACTION on EQUITY_HEADER.EQUITY_HEADER_ID = EQUITY_TRANSACTION.EQUITY_HEADER_ID

where 1=1  {0}

UNION ALL

select --HEADER
EQUITY_HEADER.EQUITY_HEADER_ID
,EQUITY_HEADER.[EQUITY_COMPANY_NAME]--AS 'Company Name'
,EQUITY_HEADER.[MATURITY_STATUS]--AS 'Maturity Status'
,EQUITY_HEADER.[FULL_NAME_EN]--AS 'Full Name (EN)'
,EQUITY_HEADER.[FULL_NAME_TH]--AS 'Full Name (TH)'
,EQUITY_HEADER.[EQUITY_TYPE_NAME]--AS 'Equity Type'
,EQUITY_HEADER.[EQUITY_SECTOR_GROUP]--AS 'Sector Group'
,EQUITY_HEADER.[EQUITY_SECTOR]--AS 'Sector'
,EQUITY_HEADER.[SYMBOL]--AS 'Symbol'
--,EQUITY_HEADER.[LISTED_STATUS]--AS 'Listed Status'
,(select REFERENCE_CODE_KEY from REFERENCE_CODE where REFERENCE_CODE_DOMAIN='RELATED_NONRELATED' and REFERENCE_CODE_VALUE_EN = EQUITY_HEADER.[LISTED_STATUS] ) as LISTED_STATUS
,EQUITY_HEADER.[BROKER]--AS 'Broker'
,EQUITY_HEADER.[CAPITAL_REGISTERED]--AS 'Capital Registered'
,EQUITY_HEADER.[CAPITAL_PAID]--AS 'Capital Paid'
,EQUITY_HEADER.[INVOLVED_PARTY_TYPE_NAME]--AS 'Involved Party Type Name'
,(select INVOLVED_PARTY_TYPE_CODE from dbo.INVOLVED_PARTY_TYPE IPT where IPT.INVOLVED_PARTY_TYPE_NAME= EQUITY_HEADER.INVOLVED_PARTY_TYPE_NAME)
,(select INVOLVED_PARTY_TYPE_REFERENCE from dbo.INVOLVED_PARTY_TYPE IPT where IPT.INVOLVED_PARTY_TYPE_NAME= EQUITY_HEADER.INVOLVED_PARTY_TYPE_NAME)
,EQUITY_HEADER.[REMAIN_UNIT]--AS 'Remain Unit'
,EQUITY_HEADER.[BID_PRICE_DATE]--AS 'Bid Price Date'
,EQUITY_HEADER.[BID_PRICE_PER_UNIT]--AS 'Bid Price per Unit'
,0 AS BID_AMOUNT--not found field
,EQUITY_HEADER.[BV_PRICE_DATE]--AS 'BV Price Date'
,EQUITY_HEADER.[BV_PRICE_PER_UNIT]--AS 'BV Price per Unit'
,0 AS BV_AMOUNT--AS 'BV AMOUNT'--not found field
,EQUITY_HEADER.[FV_PRICE_DATE]--AS 'FV Price Date'
,EQUITY_HEADER.[FV_PRICE_PER_UNIT]--AS 'FV Price per Unit'
,0 AS FV_AMOUNT--AS 'FV AMOUNT'--not found field
,EQUITY_HEADER.[CLOSE_PRICE_DATE]--AS 'Close Price Date'
,EQUITY_HEADER.[CLOSE_PRICE_PER_UNIT]--AS 'Close Price per Unit'
,EQUITY_HEADER.[CLOSE_PRICE_PER_UNIT]--AS 'Close AMOUNT'--not found field
,EQUITY_HEADER.[LIQUIDITY_TIER_I_PERCENT]--AS '%Liquidity(Tier I)'
,EQUITY_HEADER.[LIQUIDITY_TIER_II_PERCENT]--AS '%Liquidity(Tier II)'
,EQUITY_HEADER.[LIQUIDITY_LOW_LIQUIDITY_PERCENT]--AS '%Liquidity(Low Liquidity)'
,(select REFERENCE_CODE_VALUE_EN from REFERENCE_CODE where REFERENCE_CODE_DOMAIN='RELATED_NONRELATED' and REFERENCE_CODE_ID =EQUITY_HEADER.[RELATED_ID] ) as RELATED_ID
,EQUITY_HEADER.[REMARK_OIC]--AS 'Remark (OIC)'
,EQUITY_HEADER.[CREATE_DATE]--AS 'CREATE DATE'
,EQUITY_HEADER.[CREATE_BY]--AS 'CREATE BY'
,EQUITY_HEADER.[MODIFY_DATE]--AS 'MODIFY DATE'
,EQUITY_HEADER.[MODIFY_BY]--AS 'MODIFY BY'
-- TRANSACTION
,'' AS 'TRANSACTION_TYPE'
,'' AS 'REF_NO'
,'' AS 'TYPEOFPAYMENT_ID'
,'' AS 'TRADE_DATE'
,'' AS 'SETTLEMENT_DATE'
,0 AS 'UNIT'
,0 AS 'PAR_UNIT'
,0 AS 'PAR_AMOUNT'
,0 AS 'PRICE_UNIT'
,0 AS 'GROSS_AMOUNT'
,0 AS 'COM_RATE'
,0 AS 'COM_AMOUNT'
,0 AS 'VAT_RATE'
,0 AS 'VAT_AMOUNT'
,0 AS 'PURCHASE_VALUE_NET'
,0 AS 'WH_TAX_RATE'
,0 AS 'WH_TAX_AMOUNT'
,0 AS 'NET_SETTLE'
,'' AS 'PHYSICAL_ID'
,'' AS 'KEEP_AT_ID'
,'' AS 'OBLIGATION_ID'
,'' AS 'OBLIGATION_NO_ID'
,'' AS 'GUARANTEE_START_DATE'
,'' AS 'GUARANTEE_END_DATE'
,'' AS 'TRANS_CREATE_DATE'
,'' AS 'TRANS_CREATE_BY'
,'' AS 'TRANS_MODIFY_DATE'
,'' AS 'TRANS_MODIFY_BY'

----PRIVILEGE
,EQUITY_PRIVILEGE.[PRIVILEGE_TYPE_NAME]--AS 'Type'
,EQUITY_PRIVILEGE.[EX_DATE]--AS 'Ex Date '
,EQUITY_PRIVILEGE.[BCD_DATE]--AS 'Book Closing Date (BCD)'
,EQUITY_PRIVILEGE.[DIV_UNIT]--AS 'DIV./UNIT'
,EQUITY_PRIVILEGE.[DIV_AMOUNT]--AS 'DIV. (AMOUNT)'
,EQUITY_PRIVILEGE.[DIV_STOCK]--AS 'DIV. - Stock'
,EQUITY_PRIVILEGE.[FRACTION_UNIT]--AS 'UNIT (เศษหุ้น)'
,EQUITY_PRIVILEGE.[FRACTION_DIV_UNIT]--AS 'DIV./UNIT (เศษหุ้น)'
,EQUITY_PRIVILEGE.[AMORTIZE_UNIT]--AS 'Amortize / Unit'
,EQUITY_PRIVILEGE.[PRICE_UNIT]--AS 'ราคาต่อหุ้น:'
,EQUITY_PRIVILEGE.[RIGHT_OLD_NEW]--AS 'Right Old : New'
,EQUITY_PRIVILEGE.[PAYMENT_DATE]--AS 'Payment Date'
,EQUITY_PRIVILEGE.[TRANNSAFER_DATE]--AS 'วันที่แปลงหุ้น'
,EQUITY_PRIVILEGE.[TRANNSAFER_PRICE]--AS 'ราคาแปลงหุ้น '
,EQUITY_PRIVILEGE.[CREATE_DATE] AS 'PRIVILEGE_CREATE_DATE'
,EQUITY_PRIVILEGE.[CREATE_BY] AS 'PRIVILEGE_CREATE_BY'
,EQUITY_PRIVILEGE.[MODIFY_DATE] AS 'PRIVILEGE_MODIFY_DATE'
,EQUITY_PRIVILEGE.[MODIFY_BY] AS 'PRIVILEGE_MODIFY_BY'
from EQUITY_HEADER
inner join EQUITY_PRIVILEGE on EQUITY_HEADER.EQUITY_HEADER_ID = EQUITY_PRIVILEGE.EQUITY_HEADER_ID

where 1=1  {1}

";

     public static string queryFixedIncome = @"
 -- 1
	SELECT  
		-- 1
	FIXINCOMESECURITIES_HEADER.[FIXINCOMESECURITIES_HEADER_ID]
		,	FIXINCOMESECURITIES_HEADER.[COMPANY_NAME]
		,	FIXINCOMESECURITIES_HEADER.[MATURITY_STATUS]
		,	FIXINCOMESECURITIES_HEADER.[FIXED_INCOME_FULLNAME_EN]
		,	FIXINCOMESECURITIES_HEADER.[ISSUER_NAME]
		,	FIXINCOMESECURITIES_HEADER.[ISSUER_TYPE_NAME]
		,	FIXINCOMESECURITIES_HEADER.[SYMBOL]
		,	FIXINCOMESECURITIES_HEADER.[GUARANTOR_TYPE_NAME]
		,	FIXINCOMESECURITIES_HEADER.[GUARANTOR_NAME]
		,	FIXINCOMESECURITIES_HEADER.[GUARANTOR_OTHER_NAME]
		,	FIXINCOMESECURITIES_HEADER.[ISIN_CODE]
		,	FIXINCOMESECURITIES_HEADER.[BOND_TYPE_NAME]
		,	FIXINCOMESECURITIES_HEADER.[LISTED_STATUS]
		,	FIXINCOMESECURITIES_HEADER.[NUMBER_TIME]
		,	FIXINCOMESECURITIES_HEADER.[INITIAL_PAR_UNIT]
		,	FIXINCOMESECURITIES_HEADER.[CURRENT_PAR_UNIT]
		,	FIXINCOMESECURITIES_HEADER.[COUPON]
		,	FIXINCOMESECURITIES_HEADER.[ISSUE_DATE]
		,	FIXINCOMESECURITIES_HEADER.[COUPON_PAYMENT_DATE]
	--	,	FIXINCOMESECURITIES_HEADER.[MATURITY_DATE]
        ,FIXINCOMESECURITIES_HEADER.MATURITY_DATE
        ,   ROUND( CAST(DATEDIFF(month, FIXINCOMESECURITIES_HEADER.MATURITY_DATE, GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 ) AS MATURITY_DATE_CAL
		,	FIXINCOMESECURITIES_HEADER.[CALCULATION_METHOD]
		,	FIXINCOMESECURITIES_HEADER.[ISSUER_TERM]
		,	FIXINCOMESECURITIES_HEADER.[PAYMENT_FREQUENCY]

		,(SELECT ISSUE_RATING FROM RATING_HISTORY 
		WHERE RATING_HISTORY_ID = (SELECT MAX(RATING_HISTORY_ID) FROM RATING_HISTORY 
		WHERE FINANCIAL_INSTRUMENT_TYPE = 'FIXINCOMESECURITIES' AND FINANCIAL_INSTRUMENT_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID)) AS ISSUE_RATING	,
		(SELECT ISSUE_CREDIT_AGENCY FROM RATING_HISTORY 
		WHERE RATING_HISTORY_ID = (SELECT MAX(RATING_HISTORY_ID) FROM RATING_HISTORY 
		WHERE FINANCIAL_INSTRUMENT_TYPE = 'FIXINCOMESECURITIES' AND FINANCIAL_INSTRUMENT_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID)) AS ISSUE_CREDIT_AGENCY	,
		(SELECT ISSUE_UPDATE_DATE FROM RATING_HISTORY 
		WHERE RATING_HISTORY_ID = (SELECT MAX(RATING_HISTORY_ID) FROM RATING_HISTORY 
		WHERE FINANCIAL_INSTRUMENT_TYPE = 'FIXINCOMESECURITIES' AND FINANCIAL_INSTRUMENT_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID)) AS ISSUE_UPDATE_DATE	,
		(SELECT ISSUE_CREDIT_AGENCY_COUNTRY FROM RATING_HISTORY 
		WHERE RATING_HISTORY_ID = (SELECT MAX(RATING_HISTORY_ID) FROM RATING_HISTORY 
		WHERE FINANCIAL_INSTRUMENT_TYPE = 'FIXINCOMESECURITIES' AND FINANCIAL_INSTRUMENT_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID)) AS ISSUE_CREDIT_AGENCY_COUNTRY	,
		
		(SELECT ISSUER_RATING FROM RATING_HISTORY 
		WHERE RATING_HISTORY_ID = (SELECT MAX(RATING_HISTORY_ID) FROM RATING_HISTORY 
		WHERE FINANCIAL_INSTRUMENT_TYPE = 'FIXINCOMESECURITIES' AND FINANCIAL_INSTRUMENT_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID)) AS ISSUER_RATING	,
		(SELECT ISSUER_CREDIT_AGENCY FROM RATING_HISTORY 
		WHERE RATING_HISTORY_ID = (SELECT MAX(RATING_HISTORY_ID) FROM RATING_HISTORY 
		WHERE FINANCIAL_INSTRUMENT_TYPE = 'FIXINCOMESECURITIES' AND FINANCIAL_INSTRUMENT_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID)) AS ISSUER_CREDIT_AGENCY	,
		(SELECT ISSUER_UPDATE_DATE FROM RATING_HISTORY 
		WHERE RATING_HISTORY_ID = (SELECT MAX(RATING_HISTORY_ID) FROM RATING_HISTORY 
		WHERE FINANCIAL_INSTRUMENT_TYPE = 'FIXINCOMESECURITIES' AND FINANCIAL_INSTRUMENT_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID)) AS ISSUER_UPDATE_DATE	,
		(SELECT ISSUER_CREDIT_AGENCY_COUNTRY FROM RATING_HISTORY 
		WHERE RATING_HISTORY_ID = (SELECT MAX(RATING_HISTORY_ID) FROM RATING_HISTORY 
		WHERE FINANCIAL_INSTRUMENT_TYPE = 'FIXINCOMESECURITIES' AND FINANCIAL_INSTRUMENT_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID)) AS ISSUER_CREDIT_AGENCY_COUNTRY	,
				
		(SELECT GUARANTOR_RATING FROM RATING_HISTORY 
		WHERE RATING_HISTORY_ID = (SELECT MAX(RATING_HISTORY_ID) FROM RATING_HISTORY 
		WHERE FINANCIAL_INSTRUMENT_TYPE = 'FIXINCOMESECURITIES' AND FINANCIAL_INSTRUMENT_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID)) AS GUARANTOR_RATING	,
		(SELECT GUARANTOR_CREDIT_AGENCY FROM RATING_HISTORY 
		WHERE RATING_HISTORY_ID = (SELECT MAX(RATING_HISTORY_ID) FROM RATING_HISTORY 
		WHERE FINANCIAL_INSTRUMENT_TYPE = 'FIXINCOMESECURITIES' AND FINANCIAL_INSTRUMENT_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID)) AS GUARANTOR_CREDIT_AGENCY	,
		(SELECT GUARANTOR_UPDATE_DATE FROM RATING_HISTORY 
		WHERE RATING_HISTORY_ID = (SELECT MAX(RATING_HISTORY_ID) FROM RATING_HISTORY 
		WHERE FINANCIAL_INSTRUMENT_TYPE = 'FIXINCOMESECURITIES' AND FINANCIAL_INSTRUMENT_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID)) AS GUARANTOR_UPDATE_DATE	,
		(SELECT GUARANTOR_CREDIT_AGENCY_COUNTRY FROM RATING_HISTORY 
		WHERE RATING_HISTORY_ID = (SELECT MAX(RATING_HISTORY_ID) FROM RATING_HISTORY 
		WHERE FINANCIAL_INSTRUMENT_TYPE = 'FIXINCOMESECURITIES' AND FINANCIAL_INSTRUMENT_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID)) AS GUARANTOR_CREDIT_AGENCY_COUNTRY	
		,	FIXINCOMESECURITIES_HEADER.[RISK_LEVEL]
		,	FIXINCOMESECURITIES_HEADER.[LIQUIDITY_TIER_I_PERCENT]
		,	FIXINCOMESECURITIES_HEADER.[LIQUIDITY_TIER_II_PERCENT]
		,	FIXINCOMESECURITIES_HEADER.[LIQUIDITY_LOW_LIQUIDITY_PERCENT]
		,	FIXINCOMESECURITIES_HEADER.[INVOLVED_PARTY_TYPE_NAME] 

		,(select INVOLVED_PARTY_TYPE_CODE from INVOLVED_PARTY_TYPE 
		where INVOLVED_PARTY_TYPE.INVOLVED_PARTY_TYPE_ID= FIXINCOMESECURITIES_HEADER.INVOLVED_PARTY_TYPE_ID) AS INVOLVED_PARTY_TYPE_CODE
		,(select INVOLVED_PARTY_TYPE_REFERENCE from INVOLVED_PARTY_TYPE 
		where INVOLVED_PARTY_TYPE.INVOLVED_PARTY_TYPE_ID= FIXINCOMESECURITIES_HEADER.INVOLVED_PARTY_TYPE_ID) AS INVOLVED_PARTY_TYPE_REFERENCE

		-- 2
		,	FIXINCOMESECURITIES_PURCHASE.[TRANSACTION_TYPE]
		,	FIXINCOMESECURITIES_PURCHASE.[REF_NO]
		,	FIXINCOMESECURITIES_PURCHASE.[TYPEOFPAYMENT_NAME]

		,	FIXINCOMESECURITIES_PURCHASE.TIME2MATURE
		,	FIXINCOMESECURITIES_PURCHASE.TRADE_DATE
		,	FIXINCOMESECURITIES_PURCHASE.[SETTLEMENT_DATE]
		,	FIXINCOMESECURITIES_PURCHASE.[UNIT]
		,	FIXINCOMESECURITIES_PURCHASE.[TOTALSIZE]
		,	FIXINCOMESECURITIES_PURCHASE.[PAR]
		,	FIXINCOMESECURITIES_PURCHASE.[PAR_VALUE]
		,	FIXINCOMESECURITIES_PURCHASE.[YIELD]
		,	FIXINCOMESECURITIES_PURCHASE.[CLEAN_PRICE]
		,	FIXINCOMESECURITIES_PURCHASE.[AI]
		,	FIXINCOMESECURITIES_PURCHASE.[GROSS_PRICE]
		,	FIXINCOMESECURITIES_PURCHASE.[DISCOUNT_PREMIUM]
		,	FIXINCOMESECURITIES_PURCHASE.[PHYSICAL]
		,	FIXINCOMESECURITIES_PURCHASE.[KEEP_AT]
		,	FIXINCOMESECURITIES_PURCHASE.[COUNTERPARTY_NAME]
		,	FIXINCOMESECURITIES_PURCHASE.[PURCHASE_VALUE_TH]
		,	FIXINCOMESECURITIES_PURCHASE.[CURRENCY]
		,	FIXINCOMESECURITIES_PURCHASE.[EXCH_RATE]
		,	FIXINCOMESECURITIES_PURCHASE.[PURCHASE_VALUE_FOREIGN]
		,	FIXINCOMESECURITIES_PURCHASE.[RELATED]
		,	FIXINCOMESECURITIES_PURCHASE.[INDEX_RATIO]
		,	FIXINCOMESECURITIES_PURCHASE.[REMARK_OIC]
		,	FIXINCOMESECURITIES_PURCHASE.[REMAIN_UNIT]
		-- 3
		 ,	'' AS TRANSACTION_TYPE_transaction
		 ,'' AS Trade_Date_transaction
		 ,'' AS Settlement_Date_transaction

		,	0 AS UNIT_transaction
		,	0 AS YIELD_transaction
		,	'' AS COUNTERPARTY_NAME_transaction
		,	0 AS CLEANPRICE_transaction
		,	'' AS TYPEOFPAYMENT_NAME_transaction
		,	0 AS AI_transaction
		,	'' AS REF_NO_transaction
		,	0 AS GROSS_PRICE_transaction
		,	'' AS OBLIGATION_transaction
		,	'' AS GUARANTEE_TYPE_transaction
		,	'' AS OBLIGATION_NO_transaction
		,	'' AS GUARANTEE_STARTDATE_transaction
		,	'' AS GUARANTEE_ENDDATE_transaction
	  FROM FIXINCOMESECURITIES_HEADER
		   inner join FIXINCOMESECURITIES_PURCHASE
		   on FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID = FIXINCOMESECURITIES_PURCHASE.FIXINCOMESECURITIES_HEADER_ID
		   
		  where 1 = 1 {0} 
		   
	--where
	--  FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID = 2		 
 UNION ALL
	SELECT
		-- 1
FIXINCOMESECURITIES_HEADER.[FIXINCOMESECURITIES_HEADER_ID]
		, 	 FIXINCOMESECURITIES_HEADER.[COMPANY_NAME]
		,	FIXINCOMESECURITIES_HEADER.[MATURITY_STATUS]
		,	FIXINCOMESECURITIES_HEADER.[FIXED_INCOME_FULLNAME_EN]
		,	FIXINCOMESECURITIES_HEADER.[ISSUER_NAME]
		,	FIXINCOMESECURITIES_HEADER.[ISSUER_TYPE_NAME]
		,	FIXINCOMESECURITIES_HEADER.[SYMBOL]
		,	FIXINCOMESECURITIES_HEADER.[GUARANTOR_TYPE_NAME]
		,	FIXINCOMESECURITIES_HEADER.[GUARANTOR_NAME]
		,	FIXINCOMESECURITIES_HEADER.[GUARANTOR_OTHER_NAME]
		,	FIXINCOMESECURITIES_HEADER.[ISIN_CODE]
		,	FIXINCOMESECURITIES_HEADER.[BOND_TYPE_NAME]
		,	FIXINCOMESECURITIES_HEADER.[LISTED_STATUS]
		,	FIXINCOMESECURITIES_HEADER.[NUMBER_TIME]
		,	FIXINCOMESECURITIES_HEADER.[INITIAL_PAR_UNIT]
		,	FIXINCOMESECURITIES_HEADER.[CURRENT_PAR_UNIT]
		,	FIXINCOMESECURITIES_HEADER.[COUPON]
		,	FIXINCOMESECURITIES_HEADER.[ISSUE_DATE]
		,	FIXINCOMESECURITIES_HEADER.[COUPON_PAYMENT_DATE]
	--	,	FIXINCOMESECURITIES_HEADER.[MATURITY_DATE]
        ,FIXINCOMESECURITIES_HEADER.MATURITY_DATE
        ,   ROUND( CAST(DATEDIFF(month, FIXINCOMESECURITIES_HEADER.MATURITY_DATE, GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 ) AS MATURITY_DATE_CAL
		,	FIXINCOMESECURITIES_HEADER.[CALCULATION_METHOD]
		,	FIXINCOMESECURITIES_HEADER.[ISSUER_TERM]
		,	FIXINCOMESECURITIES_HEADER.[PAYMENT_FREQUENCY]
		
		,(SELECT ISSUE_RATING FROM RATING_HISTORY 
		WHERE RATING_HISTORY_ID = (SELECT MAX(RATING_HISTORY_ID) FROM RATING_HISTORY 
		WHERE FINANCIAL_INSTRUMENT_TYPE = 'FIXINCOMESECURITIES' AND FINANCIAL_INSTRUMENT_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID)) AS ISSUE_RATING	,
		(SELECT ISSUE_CREDIT_AGENCY FROM RATING_HISTORY 
		WHERE RATING_HISTORY_ID = (SELECT MAX(RATING_HISTORY_ID) FROM RATING_HISTORY 
		WHERE FINANCIAL_INSTRUMENT_TYPE = 'FIXINCOMESECURITIES' AND FINANCIAL_INSTRUMENT_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID)) AS ISSUE_CREDIT_AGENCY	,
		(SELECT ISSUE_UPDATE_DATE FROM RATING_HISTORY 
		WHERE RATING_HISTORY_ID = (SELECT MAX(RATING_HISTORY_ID) FROM RATING_HISTORY 
		WHERE FINANCIAL_INSTRUMENT_TYPE = 'FIXINCOMESECURITIES' AND FINANCIAL_INSTRUMENT_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID)) AS ISSUE_UPDATE_DATE	,
		(SELECT ISSUE_CREDIT_AGENCY_COUNTRY FROM RATING_HISTORY 
		WHERE RATING_HISTORY_ID = (SELECT MAX(RATING_HISTORY_ID) FROM RATING_HISTORY 
		WHERE FINANCIAL_INSTRUMENT_TYPE = 'FIXINCOMESECURITIES' AND FINANCIAL_INSTRUMENT_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID)) AS ISSUE_CREDIT_AGENCY_COUNTRY	,
		
		(SELECT ISSUER_RATING FROM RATING_HISTORY 
		WHERE RATING_HISTORY_ID = (SELECT MAX(RATING_HISTORY_ID) FROM RATING_HISTORY 
		WHERE FINANCIAL_INSTRUMENT_TYPE = 'FIXINCOMESECURITIES' AND FINANCIAL_INSTRUMENT_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID)) AS ISSUER_RATING	,
		(SELECT ISSUER_CREDIT_AGENCY FROM RATING_HISTORY 
		WHERE RATING_HISTORY_ID = (SELECT MAX(RATING_HISTORY_ID) FROM RATING_HISTORY 
		WHERE FINANCIAL_INSTRUMENT_TYPE = 'FIXINCOMESECURITIES' AND FINANCIAL_INSTRUMENT_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID)) AS ISSUER_CREDIT_AGENCY	,
		(SELECT ISSUER_UPDATE_DATE FROM RATING_HISTORY 
		WHERE RATING_HISTORY_ID = (SELECT MAX(RATING_HISTORY_ID) FROM RATING_HISTORY 
		WHERE FINANCIAL_INSTRUMENT_TYPE = 'FIXINCOMESECURITIES' AND FINANCIAL_INSTRUMENT_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID)) AS ISSUER_UPDATE_DATE	,
		(SELECT ISSUER_CREDIT_AGENCY_COUNTRY FROM RATING_HISTORY 
		WHERE RATING_HISTORY_ID = (SELECT MAX(RATING_HISTORY_ID) FROM RATING_HISTORY 
		WHERE FINANCIAL_INSTRUMENT_TYPE = 'FIXINCOMESECURITIES' AND FINANCIAL_INSTRUMENT_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID)) AS ISSUER_CREDIT_AGENCY_COUNTRY	,
				
		(SELECT GUARANTOR_RATING FROM RATING_HISTORY 
		WHERE RATING_HISTORY_ID = (SELECT MAX(RATING_HISTORY_ID) FROM RATING_HISTORY 
		WHERE FINANCIAL_INSTRUMENT_TYPE = 'FIXINCOMESECURITIES' AND FINANCIAL_INSTRUMENT_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID)) AS GUARANTOR_RATING	,
		(SELECT GUARANTOR_CREDIT_AGENCY FROM RATING_HISTORY 
		WHERE RATING_HISTORY_ID = (SELECT MAX(RATING_HISTORY_ID) FROM RATING_HISTORY 
		WHERE FINANCIAL_INSTRUMENT_TYPE = 'FIXINCOMESECURITIES' AND FINANCIAL_INSTRUMENT_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID)) AS GUARANTOR_CREDIT_AGENCY	,
		(SELECT GUARANTOR_UPDATE_DATE FROM RATING_HISTORY 
		WHERE RATING_HISTORY_ID = (SELECT MAX(RATING_HISTORY_ID) FROM RATING_HISTORY 
		WHERE FINANCIAL_INSTRUMENT_TYPE = 'FIXINCOMESECURITIES' AND FINANCIAL_INSTRUMENT_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID)) AS GUARANTOR_UPDATE_DATE	,
		(SELECT GUARANTOR_CREDIT_AGENCY_COUNTRY FROM RATING_HISTORY 
		WHERE RATING_HISTORY_ID = (SELECT MAX(RATING_HISTORY_ID) FROM RATING_HISTORY 
		WHERE FINANCIAL_INSTRUMENT_TYPE = 'FIXINCOMESECURITIES' AND FINANCIAL_INSTRUMENT_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID)) AS GUARANTOR_CREDIT_AGENCY_COUNTRY	

		,	FIXINCOMESECURITIES_HEADER.[RISK_LEVEL]
		,	FIXINCOMESECURITIES_HEADER.[LIQUIDITY_TIER_I_PERCENT]
		,	FIXINCOMESECURITIES_HEADER.[LIQUIDITY_TIER_II_PERCENT]
		,	FIXINCOMESECURITIES_HEADER.[LIQUIDITY_LOW_LIQUIDITY_PERCENT]
		,	FIXINCOMESECURITIES_HEADER.[INVOLVED_PARTY_TYPE_NAME] 
		,(select INVOLVED_PARTY_TYPE_CODE from INVOLVED_PARTY_TYPE 
		where INVOLVED_PARTY_TYPE.INVOLVED_PARTY_TYPE_ID= FIXINCOMESECURITIES_HEADER.INVOLVED_PARTY_TYPE_ID) AS INVOLVED_PARTY_TYPE_CODE
		,(select INVOLVED_PARTY_TYPE_REFERENCE from INVOLVED_PARTY_TYPE 
		where INVOLVED_PARTY_TYPE.INVOLVED_PARTY_TYPE_ID= FIXINCOMESECURITIES_HEADER.INVOLVED_PARTY_TYPE_ID) AS INVOLVED_PARTY_TYPE_REFERENCE

		-- 2
			,	 '' AS TRANSACTION_TYPE
			,	 '' AS REF_NO
			,	 '' AS TYPEOFPAYMENT_NAME

			,	 0 AS TIME2MATURE
			,	 '' AS TRADE_DATE
			
			,	 '' AS SETTLEMENT_DATE
			,	0 AS UNIT
			,	0 AS TOTALSIZE
			,	0 AS PAR
			,	0 AS PAR_VALUE
			,	0 AS YIELD
			,	0 AS CLEAN_PRICE
			,	0 AS AI
			,	0 AS GROSS_PRICE
			,	0 AS DISCOUNT_PREMIUM
			,	 '' AS PHYSICAL
			,	 '' AS KEEP_AT
			,	 '' AS COUNTERPARTY_NAME
			,	0 AS PURCHASE_VALUE_TH
			,	 '' AS CURRENCY
			,	0 AS EXCH_RATE
			,	0 AS PURCHASE_VALUE_FOREIGN
			,	 '' AS RELATED
			,	 '' AS INDEX_RATIO
			,	 '' AS REMARK_OIC
			,	0 AS REMAIN_UNIT

		-- 3
		,	FIXINCOMESECURITIES_TRANSACTION.[TRANSACTION_TYPE]

		,   FIXINCOMESECURITIES_TRANSACTION.TRADE_DATE AS Trade_Date_transaction
		,   FIXINCOMESECURITIES_TRANSACTION.SETTLEMENT_DATE AS Settlement_Date_transaction
		,	FIXINCOMESECURITIES_TRANSACTION.[UNIT]
		,	FIXINCOMESECURITIES_TRANSACTION.[YIELD]
		,	FIXINCOMESECURITIES_TRANSACTION.[COUNTERPARTY_NAME]
		,	FIXINCOMESECURITIES_TRANSACTION.[CLEANPRICE]
		,	FIXINCOMESECURITIES_TRANSACTION.[TYPEOFPAYMENT_NAME]
		,	FIXINCOMESECURITIES_TRANSACTION.[AI]
		,	FIXINCOMESECURITIES_TRANSACTION.[REF_NO]
		,	FIXINCOMESECURITIES_TRANSACTION.[GROSS_PRICE]
		,	(SELECT OBLIGATION_NAME FROM OBLIGATION WHERE OBLIGATION.OBLIGATION_ID = FIXINCOMESECURITIES_TRANSACTION.OBLIGATION ) as OBLIGATION_transaction
		,	FIXINCOMESECURITIES_TRANSACTION.[GUARANTEE_TYPE]
		,	(SELECT OBLIGATION_NO_NAME FROM OBLIGATION_NO WHERE OBLIGATION_NO.OBLIGATION_NO_ID = FIXINCOMESECURITIES_TRANSACTION.OBLIGATION_NO ) as OBLIGATION_NO_transaction
		,	FIXINCOMESECURITIES_TRANSACTION.[GUARANTEE_STARTDATE]
		,	FIXINCOMESECURITIES_TRANSACTION.[GUARANTEE_ENDDATE]

	FROM FIXINCOMESECURITIES_HEADER
		   inner join FIXINCOMESECURITIES_TRANSACTION
		   on FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID = FIXINCOMESECURITIES_TRANSACTION.FIXINCOMESECURITIES_HEADER_ID
    where 1=1 {1}
 
";

     public static string queryUnitTrusts = @"  
SELECT 
	 -- 1.
        UNIT_TRUST_HEADER.[UNIT_TRUST_HEADER_ID]
	 	,   UNIT_TRUST_HEADER.[UNIT_TRUST_COMPANY_NAME]
		,	UNIT_TRUST_HEADER.[MATURITY_STATUS]
		,	UNIT_TRUST_HEADER.[UNIT_TRUST_TYPE_NAME]
		,	UNIT_TRUST_HEADER.[UNIT_TRUST_TYPE_GROUP]
		,	UNIT_TRUST_HEADER.[FUND_NAME]
		,	UNIT_TRUST_HEADER.[LISTED_STATUS]
		,	UNIT_TRUST_HEADER.[ASSET_MGMT_NAME]
		,	UNIT_TRUST_HEADER.[SYMBOL]
		,	UNIT_TRUST_HEADER.[TOTAL_SIZE]
		,	UNIT_TRUST_HEADER.[MATURITY_DATE]
        ,   ROUND( CAST(DATEDIFF(month, UNIT_TRUST_HEADER.MATURITY_DATE, GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 ) AS MATURITY_DATE_CAL
		,	UNIT_TRUST_HEADER.[BID_PRICE]
		,	UNIT_TRUST_HEADER.[INITIAL_PAR]
		,	UNIT_TRUST_HEADER.[BID_PRICE_DATE]
		,	UNIT_TRUST_HEADER.[PAR]
		,	UNIT_TRUST_HEADER.[PAR_DATE]
		,	UNIT_TRUST_HEADER.[LIQUIDITY_TIER_I_PERCENT]
		,	UNIT_TRUST_HEADER.[LIQUIDITY_TIER_II_PERCENT]
		,	UNIT_TRUST_HEADER.[LIQUIDITY_LOW_LIQUIDITY_PERCENT]
		,	UNIT_TRUST_HEADER.[INVOLVED_PARTY_TYPE_NAME]
		,(select INVOLVED_PARTY_TYPE_CODE from INVOLVED_PARTY_TYPE 
		where INVOLVED_PARTY_TYPE.INVOLVED_PARTY_TYPE_ID= UNIT_TRUST_HEADER.INVOLVED_PARTY_TYPE_ID) AS INVOLVED_PARTY_TYPE_CODE
		,(select INVOLVED_PARTY_TYPE_REFERENCE from INVOLVED_PARTY_TYPE 
		where INVOLVED_PARTY_TYPE.INVOLVED_PARTY_TYPE_ID= UNIT_TRUST_HEADER.INVOLVED_PARTY_TYPE_ID) AS INVOLVED_PARTY_TYPE_REFERENCE
		,	UNIT_TRUST_HEADER.[RELATED]
		,	UNIT_TRUST_HEADER.[REMARK_OIC]
	--2.
		,	UNIT_TRUST_TRANSACTION.[TRANSACTION_TYPE]
		,	UNIT_TRUST_TRANSACTION.[REF_NO]
		,	UNIT_TRUST_TRANSACTION.[TYPEOFPAYMENT_NAME]
		,	UNIT_TRUST_TRANSACTION.[TRADE_DATE]
		,	UNIT_TRUST_TRANSACTION.[SETTLEMENT_DATE]
		,	UNIT_TRUST_TRANSACTION.[UNIT]
		,	UNIT_TRUST_TRANSACTION.[PRICE_UNIT]
		,	UNIT_TRUST_TRANSACTION.[GROSS_AMOUNT]
		,	UNIT_TRUST_TRANSACTION.[COM_RATE]
		,	UNIT_TRUST_TRANSACTION.[COM_AMOUNT]
		,	UNIT_TRUST_TRANSACTION.[VAT_RATE]
		,	UNIT_TRUST_TRANSACTION.[VAT_AMOUNT]
		,	UNIT_TRUST_TRANSACTION.[PURCHASE_VALUE_NET]
		,	UNIT_TRUST_TRANSACTION.[WH_TAX_RATE]
		,	UNIT_TRUST_TRANSACTION.[WH_TAX_AMOUNT]
		,	UNIT_TRUST_TRANSACTION.[NET_SETTLE]
		,	UNIT_TRUST_TRANSACTION.[PHYSICAL]
		--,	UNIT_TRUST_TRANSACTION.[KEEP_AT]
        ,   (select REFERENCE_CODE.REFERENCE_CODE_VALUE_EN 
			 from REFERENCE_CODE
			 where REFERENCE_CODE.REFERENCE_CODE_ID = UNIT_TRUST_TRANSACTION.KEEP_AT
			 )AS KEEP_AT
		,	UNIT_TRUST_TRANSACTION.[OBLIGATION]
		,	UNIT_TRUST_TRANSACTION.[OBLIGATION_NO]
		,	UNIT_TRUST_TRANSACTION.[GUARANTEE_START_DATE]
		,	UNIT_TRUST_TRANSACTION.[GUARANTEE_END_DATE]
		-- 3.
		,	 '' AS Unit_number_of_holding
		,	 '' AS Type_
		,	 ''  AS EX_DATE
		,	 ''  AS BCD_DATE
		,	 '' AS TypeOfPayment
		,	 ''  AS PAYMENT_DATE
		,	0 AS DIV_UNIT
		,	0 AS DIV_AMOUNT
		,	0 AS RIGHT_OLD_NEW
		,	0 AS DIV_STOCK
		,	0 AS FRACTION_UNIT
		,	0 AS FRACTION_DIV_UNIT
		,	0 AS AMORTIZE_UNIT
		,	0 AS PRICE_UNIT_privilege
		,	 ''  AS TRANNSAFER_DATE
		,	0 AS TRANNSAFER_PRICE


  FROM UNIT_TRUST_HEADER
	   LEFT join  UNIT_TRUST_TRANSACTION 
	   on UNIT_TRUST_HEADER.UNIT_TRUST_HEADER_ID = UNIT_TRUST_TRANSACTION.UNIT_TRUST_TRANSACTION_HEADER_ID
WHERE 1=1 {0}
	 -- UNIT_TRUST_HEADER.UNIT_TRUST_HEADER_ID = 1
	  
UNION ALL
	  
SELECT 
	-- 1.
    UNIT_TRUST_HEADER.[UNIT_TRUST_HEADER_ID]
	,   UNIT_TRUST_HEADER.[UNIT_TRUST_COMPANY_NAME]
	,	UNIT_TRUST_HEADER.[MATURITY_STATUS]
	,	UNIT_TRUST_HEADER.[UNIT_TRUST_TYPE_NAME]
	,	UNIT_TRUST_HEADER.[UNIT_TRUST_TYPE_GROUP]
	,	UNIT_TRUST_HEADER.[FUND_NAME]
	,	UNIT_TRUST_HEADER.[LISTED_STATUS]
	,	UNIT_TRUST_HEADER.[ASSET_MGMT_NAME]
	,	UNIT_TRUST_HEADER.[SYMBOL]
	,	UNIT_TRUST_HEADER.[TOTAL_SIZE]
	,	UNIT_TRUST_HEADER.[MATURITY_DATE]
    ,   ROUND( CAST(DATEDIFF(month, UNIT_TRUST_HEADER.MATURITY_DATE, GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 ) AS MATURITY_DATE_CAL
	,	UNIT_TRUST_HEADER.[BID_PRICE]
	,	UNIT_TRUST_HEADER.[INITIAL_PAR]
	,	UNIT_TRUST_HEADER.[BID_PRICE_DATE]
	,	UNIT_TRUST_HEADER.[PAR]
	,	UNIT_TRUST_HEADER.[PAR_DATE]
	,	UNIT_TRUST_HEADER.[LIQUIDITY_TIER_I_PERCENT]
	,	UNIT_TRUST_HEADER.[LIQUIDITY_TIER_II_PERCENT]
	,	UNIT_TRUST_HEADER.[LIQUIDITY_LOW_LIQUIDITY_PERCENT]
	,	UNIT_TRUST_HEADER.[INVOLVED_PARTY_TYPE_NAME]
,(select INVOLVED_PARTY_TYPE_CODE from INVOLVED_PARTY_TYPE 
		where INVOLVED_PARTY_TYPE.INVOLVED_PARTY_TYPE_ID= UNIT_TRUST_HEADER.INVOLVED_PARTY_TYPE_ID) AS INVOLVED_PARTY_TYPE_CODE
		,(select INVOLVED_PARTY_TYPE_REFERENCE from INVOLVED_PARTY_TYPE 
		where INVOLVED_PARTY_TYPE.INVOLVED_PARTY_TYPE_ID= UNIT_TRUST_HEADER.INVOLVED_PARTY_TYPE_ID) AS INVOLVED_PARTY_TYPE_REFERENCE
	,	UNIT_TRUST_HEADER.[RELATED]
	,	UNIT_TRUST_HEADER.[REMARK_OIC]
	-- 2.
	,	 ''  AS TRANSACTION_TYPE
	,	 ''  AS REF_NO
	,	 ''  AS TYPEOFPAYMENT_NAME
	,	 ''  AS TRADE_DATE
	,	 ''  AS SETTLEMENT_DATE
	,	0 AS UNIT
	,	0 AS PRICE_UNIT
	,	0 AS GROSS_AMOUNT
	,	0 AS COM_RATE
	,	0 AS COM_AMOUNT
	,	0 AS VAT_RATE
	,	0 AS VAT_AMOUNT
	,	0 AS PURCHASE_VALUE_NET
	,	0 AS WH_TAX_RATE
	,	0 AS WH_TAX_AMOUNT
	,	0 AS NET_SETTLE
	,	 ''  AS PHYSICAL
	,	 ''  AS KEEP_AT
	,	 ''  AS OBLIGATION
	,	 ''  AS OBLIGATION_NO
	,	 ''  AS GUARANTEE_START_DATE
	,	 ''  AS GUARANTEE_END_DATE
	-- 3.
	,	 '' AS Unit_number_of_holding
	,	 '' AS Type
	,	unit_trust_privilege.[EX_DATE]
    ,	unit_trust_privilege.[BCD_DATE]
	,	 '' AS TypeOfPayment
	
	,	unit_trust_privilege.[PAYMENT_DATE]
	,	unit_trust_privilege.[DIV_UNIT]
	,	unit_trust_privilege.[DIV_AMOUNT]
	,	unit_trust_privilege.[RIGHT_OLD_NEW]
	,	unit_trust_privilege.[DIV_STOCK]
	,	unit_trust_privilege.[FRACTION_UNIT]
	,	unit_trust_privilege.[FRACTION_DIV_UNIT]
	,	unit_trust_privilege.[AMORTIZE_UNIT]
	,	unit_trust_privilege.[PRICE_UNIT] as PRICE_UNIT_privilege
	,	unit_trust_privilege.[TRANNSAFER_DATE]
	,	unit_trust_privilege.[TRANNSAFER_PRICE]


  FROM UNIT_TRUST_HEADER
	   LEFT join  unit_trust_privilege
	   on UNIT_TRUST_HEADER.UNIT_TRUST_HEADER_ID = unit_trust_privilege.UNIT_TRUST_HEADER_ID
    where 1=1 {1}
";
    }
}
